package cn.jvtc.course.management.system.utils;


import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Array;
import java.util.*;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;


/**
 * 用于操作Excel文件的工具类
 *
 * @author ZengZhengFang
 * @date 2017-12-06 16:36:56
 **/
public class ExcelUtils {
    private Workbook wb;
    private Sheet sheet;
    private Row row;

    public ExcelUtils(String filepath) {
        if(filepath==null){
            return;
        }
        String ext = filepath.substring(filepath.lastIndexOf("."));
        try {
            InputStream is = new FileInputStream(filepath);
            if(".xls".equals(ext)){
                wb = new HSSFWorkbook(is);
            }else if(".xlsx".equals(ext)){
                wb = new XSSFWorkbook(is);
            }else{
                wb=null;
            }
        } catch (FileNotFoundException e) {
        } catch (IOException e) {
        }
    }

    /**
     * 读取Excel数据内容
     * @param sheetIndex 读第几个表
     * @param beginRow 从第几行开始读
     * @return List 包含整个excel数据的List对象
     * @author ZengZhengFang
     */
    public List<LinkedList<String>> readExcelContent(int beginRow,int sheetIndex) throws Exception{
        if(wb==null){
            throw new Exception("读取失败！");
        }
        sheet = wb.getSheetAt(sheetIndex - 1);
        // 总行数
        int rowNum = sheet.getLastRowNum();
        row = sheet.getRow(0);
        int colNum = row.getPhysicalNumberOfCells();
        ArrayList<LinkedList<String>> result = new ArrayList<>();
        for (int i = beginRow - 1; i <= rowNum; i++) {
            row = sheet.getRow(i);
            LinkedList<String> list = new LinkedList<>();
            for(int j = 0;j < colNum;j++) {
                String str = getCellFormatValue(row.getCell(j));
                if(!"".equals(str)) {
                    list.add(str);
                }
            }
            if(!list.isEmpty()) {
                result.add(list);
            }
        }
        return result;
    }

    /**
     *
     * 根据Cell类型设置数据
     *
     * @param cell
     * @return
     * @author ZengZhengFang
     */
    private String getCellFormatValue(Cell cell) {
        String cellvalue = "";
        if (cell != null) {
            switch (cell.getCellType()) {
                case Cell.CELL_TYPE_NUMERIC:
                    cellvalue = cell.getNumericCellValue() + "";
                    break;
                case Cell.CELL_TYPE_FORMULA:
                    if (DateUtil.isCellDateFormatted(cell)) {
                        // 如果为Date类型
                        cellvalue = cell.getDateCellValue().toString();
                    } else {
                        // 如果是纯数字
                        cellvalue = String.valueOf(cell.getNumericCellValue());
                    }
                    break;
                case Cell.CELL_TYPE_STRING:
                    cellvalue = cell.getRichStringCellValue().getString();
                    break;
                default:
                    cellvalue = "";
            }
        }
        return cellvalue;
    }

    public static void main(String[] args) {
        try {
            ExcelUtils excelReader = new ExcelUtils("h:\\poi_test\\test.xls");
            List<LinkedList<String>> result = excelReader.readExcelContent(1,2);
            for(LinkedList<String> list : result) {
                System.out.println(Arrays.toString(list.toArray()));
            }
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }catch (Exception e) {
            e.printStackTrace();
        }
    }
}


